Skip to main content

Leveraging Gen AI in Templates

Useful Macro Functions​

Overview​

There are a few macro functions related to AI Assistant usage. These are described below.

vector = embeddings(string, aiAssistantAlias)

Obtain a vector list for the given string. The alias specifies which configured AI service to use. A null means to use the default.

{usage, completionText} = completion(prompt, alias [, returnOnlyCompletionText] )

Obtain a completion document for the given prompt. The prompt is used as the messages argument typically in the form of [ {role: "user", content: prompt} ]. The alias specifies which configured AI service to use. A null means to use the default. If returnOnlyCompletionTextis true then only the completion text is returned.

tsvText = formattedResult(inActiveChunk)

Return tab separated text for the current state of the template result. When inActiveChunk is false or not provided, the overall current text is returned. When inActiveChunk is true and the formula is on a group summary then that group's header, inside content, and summary up to the cell are returned. When inActiveChunk is true and the formula is on a report summary then the report summary up to the cell is returned.

Using AI Completion in Templates​

A common use case for completion interactions is to provide text to an AI assistant which includes a prompt for the assistant to answer. The formattedResult() and completion() macro functions can be used together to dynamically generate report content using generative AI techniques. Shown below is the portfolio analysis output which has generated comments for the stock holdings, bond holdings and overall allocation.

  

To achieve this an AI Assistant prompt is generated using tab separated text for each summary area. The formattedResult(true) cells obtain the tab separated text. These cells suppress their output from the result as the text is used in the nearby completion macro.

The stock group summary line has these cells.

  

The bond group summary line has these cells.

  

Sample “formattedResult(true)” output is

Bonds

2030 New York State Thruway Authority 4.5% $40,000.00 62%

2025 California Municipal 7.5% $25,000.00 38%

$65,000.00

Total portfolio value $616,275.00

Comments

Per the 3rd cell in the bond group summary line, this output is concatenated as part of what is sent to the completion() macro function to form the effective “prompt” to the AI endpoint.

The report summary line has these cells.

  

Interacting with Results​

NOTE- Depending on how the AI Assistant is configured by the Qarbine administrator the template and query results may have content that your company policy does not want to be sent to a 3rd party AI service.

Data Source​

Qarbine can query a side variety of data flavors and present them in the Data Source Designer. Below is a simple MongoDB example.

  

There are pop up menu options as shown below.

  

Choosing the second menu option shows a dialog for user input. A sample is shown below.

  

Click

  

The user’s entered text and the tab delimited results are sent to the AI Assistant. Below is a sample result.

  

Below is the result from another interaction. It uses an Open AI service

  

Notice Open AI mis-parsed the provided tab delimited data. Kittycat’s age is 6 and Mouser’s age is 4. The total of the ages is 6+6+6+4 = 22. The average is 5.5 years of age.

The same text was sent to Microsoft Azure AI and it produced the following.

  

Perhaps since the ages were integers it chose to provide a closest integer result?

Here is the result from Google AI.

  

Analytic Report Results​

The presentation of analytic template results includes a pop up menu option for AI Assistant interactions. Shown below is the pop up menu after a portfolio template was run.

  

Choosing the option highlighted presents a dialog for the user to enter a prompt and also choose which AI Assistant to ask.

  

Shown below is a sample response. The response can vary quite dramatically across the different AI Assistants.

  

You can also choose one of the other popup menu options to get text into the clipboard and then paste it into some other tool that interacts with a generative AI service.

Interactive AI Driven Queries​

Overview​

When running analytic templates Qarbine can accept input from a user and use it to dynamically generate a query to retrieve data processed by a template.

Note, currently only MongoDB query language (MQL) based queries are supported.

AI Generated Queries from User Input​

When the RAG aware template is run the user is presented with a dialog allowing free form input which is used by an AI Assistant.

  

Below that text area is schema information about the intended set of data. In this case the template is defined to present information from documents in the MongoDB sample AirBnB listingsAndReviews collection.

  

The user enters text such as that shown below.

All the properties with an address in the country of United States less than 500 in price.

The user clicks

  

The user input along with the template provided context information is used to dynamically define the retrieval query through an AI assistant configured within Qarbine.

  

The analysis runs and the results are then presented. The first match is shown below.

  

Reviewing the RAG Aware Template​

There are a few elements that together achieve the user interaction described above. The template defines

  • an inline data source referencing @generatedByAiAssistant and
  • initial values to provide context information.

Below is the main data retrieval definition using an inline data source. The data service and database are required. The query is simply a placeholder variable for the output from an AI assistant. As noted above, the data service must refer to a MongoDB language compatible data service.

  

. . .

  

The “@generatedByAiAssistant variable is well known by Qarbine and triggers the RAG dialog execution flow. The main data parameters and other information are used to present the RAG input dialog. Any regular Qarbine prompts run after the AI Assistant based on.

The template property dialog tab shown below is used to provide context to the AI Assistant.

  

A description of the context fields is below.

Field Description
collectionThe name of the collection which is used to provide the schema information to the RAG input dialog. The template’s inline data source references the data service and database containing the collection.
includeFieldsThe list of fields to be retrieved by the query. These should correspond to what the template uses in its formulas and cells.
promptDescriptionOptional HTML text to include in the RAG dialog.
sortFieldsOptional list of fields to sort the results. A trailing ‘+’ means ascending and a trailing ‘-’ descending.
limitOptional number of total documents to retrieve.

Consider user input of

all the properties with an address in the country of United States less than $500 in price

The user input is available in the @userInput variable. The expanded user prompt similar to what was sent to the AI service is available in the variable @expandedUserPrompt. It contains the user input plus the Qarbine added context information. That string does not include the schema and storage nature context provided on the backend service side. An example expandedUserPromptvalue is shown below.

All the properties with an address in the country of United States less than $500 in price.

Return the complete name, summary, price, country, bedrooms, address and location details. Sort the answer set by price in ascending order. Limit the answer set to 10 elements. Return only the query in your response.

The generated query is available as the variable @generatedByAiAssistant. For example,

db.listingsAndReviews.find({
  "address.country": "United States",
  "price": { $lt: "$500" }
},
{"address":1,"location":1,"name":1,"price":1,"summary":1}).sort({"price":1}).limit(10)

This is useful for debugging templates. Note above that the AI Assistant service was very literal in the price comparison. Better user input would not use the currency symbol.

all the properties with an address in the country of United States less than 500 in price

That numeric criteria nuance is included as guidance to the user in the RAG dialog.

Sometimes the generated query needs to be adjusted in order to avoid MongoDB query path collisions. This happens when a query specifies to return a document (i.e., address) and a subfield thereof (i.e., address.city). This was seen in some Open AI generated queries. If so then the original generated query is in the variable @generatedByAiAssistantOriginal.